package com.css.fxfzypg.modules.saresults.repository.impl;

import com.css.fxfzypg.modules.saresults.entity.DzqhApCruxHecEntity;
import com.css.fxfzypg.modules.saresults.entity.DzqhGroundMotionParametersEntity;
import com.css.fxfzypg.modules.saresults.param.MotionParametersParam;
import com.css.fxfzypg.modules.saresults.repository.GroundMotionParametersRepository;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.util.PlatformSessionUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class GroundMotionParametersRepositoryImpl implements GroundMotionParametersRepository {

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public void saveData(DzqhGroundMotionParametersEntity parameters) {
        jdbcTemplate.update("insert into dzqh_ground_motion_parameters  (gmp_id,gmp_code,id,zoning_code,province,city,county,township,longitude,latitude,peak_acceleration,response_spectrum,reason_for_change,approval_documents,create_user,create_time,is_delete,type,list_type,original_id ) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                parameters.getGmpId(), parameters.getGmpCode(), parameters.getId(), parameters.getZoningCode(), parameters.getProvince(), parameters.getCity(), parameters.getCounty(), parameters.getTownship(), parameters.getLongitude(), parameters.getLatitude(), parameters.getPeakAcceleration(), parameters.getResponseSpectrum(), parameters.getReasonForChange(), parameters.getApprovalDocuments(),
                parameters.getCreateUser(), parameters.getCreateTime(), parameters.getIsDelete(),parameters.getType(),parameters.getListType(),parameters.getOriginalId());
    }

    @Override
    public HashMap<String, Object> queryMotionParameters(MotionParametersParam param, int page, int size,String geom) {
        StringBuilder sql = new StringBuilder("select * from dzqh_ground_motion_parameters where is_delete = '0'");
        if(PlatformObjectUtils.isNotEmpty(param.getListType())){
            sql.append(" and list_type = '" + param.getListType() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getZoningCode())) {
            sql.append(" and zoning_code like '%" + param.getZoningCode() + "%'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getProvince())) {
            sql.append(" and province = '" + param.getProvince() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCity())) {
            sql.append(" and city = '" + param.getCity() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCounty())) {
            sql.append(" and county = '" + param.getCounty() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getTownship())) {
            sql.append(" and township = '" + param.getTownship() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getReasonForChange())) {
            sql.append(" and reason_for_change like '%" + param.getReasonForChange() + "%'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getStartLatitude())) {
            sql.append(" and latitude >= " + param.getStartLatitude());
        }
        if(PlatformObjectUtils.isNotEmpty(param.getEndLatitude())){
            sql.append(" and latitude <= " + param.getEndLatitude());
        }
        if (PlatformObjectUtils.isNotEmpty(param.getStartLongitude())) {
            sql.append(" and longitude >= " + param.getStartLongitude());
        }
        if(PlatformObjectUtils.isNotEmpty(param.getEndLongitude())){
            sql.append(" and longitude <= " + param.getEndLongitude());
        }

        if(PlatformObjectUtils.isEmpty(param.getListType())&&PlatformObjectUtils.isNotEmpty(geom)){
            sql.append(" and ST_Intersects(st_geometryfromtext('POINT(' || longitude || ' ' || latitude || ')',4490),st_geometryfromtext('"+geom+"',4490))");
        }

        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhGroundMotionParametersEntity.class)).size();
        StringBuilder limitSql = new StringBuilder(" limit " + size + " offset " + (page - 1) * size);
        sql.append(orderSql).append(limitSql);
        List<DzqhGroundMotionParametersEntity> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhGroundMotionParametersEntity.class));
        HashMap<String, Object> resultMap = new HashMap();
        resultMap.put("total", total);
        resultMap.put("rows", list);
        return resultMap;
    }

    @Override
    public DzqhGroundMotionParametersEntity getGmpByGmpId(String gmpId) {
        try {
            String sql = "select * from dzqh_ground_motion_parameters where gmp_id = '" + gmpId + "' and is_delete = '0'";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(DzqhGroundMotionParametersEntity.class));
        } catch (DataAccessException e) {
            return null;
        }
    }

    @Override
    public List<Integer> getProvinceNumber(String province){
        String sql = "select count(province) from dzqh_ground_motion_parameters where province = '"+province+"' and type = '1' " +
                "union all select count(province) from dzqh_ground_motion_parameters where province = '"+province+"'  and type = '0'";
        List<Integer> counts = jdbcTemplate.query(sql, new RowMapper<Integer>() {
            @Override
            public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs.getInt(1);
            }
        });
        return counts;
    }

    @Override
    public List<Integer> getCityNumber(String city) {
        String sql = "select count(province) from dzqh_ground_motion_parameters where city = '"+city+"' and type = '1' " +
                "union all select count(province) from dzqh_ground_motion_parameters where city = '"+city+"'  and type = '0'";
        List<Integer> counts = jdbcTemplate.query(sql, new RowMapper<Integer>() {
            @Override
            public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs.getInt(1);
            }
        });
        return counts;
    }

    @Override
    public List<Integer> getCountyNumber(String county) {
        String sql = "select count(province) from dzqh_ground_motion_parameters where county = '"+county+"' and type = '1' " +
                "union all select count(province) from dzqh_ground_motion_parameters where county = '"+county+"'  and type = '0'";
        List<Integer> counts = jdbcTemplate.query(sql, new RowMapper<Integer>() {
            @Override
            public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs.getInt(1);
            }
        });
        return counts;
    }

    @Override
    public void deleteDataById(String gmpId) {
        String sql = "update dzqh_ground_motion_parameters set is_delete = '1' where gmp_id = '"+gmpId+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public DzqhGroundMotionParametersEntity getGmpByOriginalId(String originalId) {
        try {
            String sql = "select * from dzqh_ground_motion_parameters where gmp_id = '" + originalId + "'";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(DzqhGroundMotionParametersEntity.class));
        } catch (DataAccessException e) {
            return null;
        }
    }

    @Override
    public void updateDataByOriginalId(DzqhGroundMotionParametersEntity entity) {
       jdbcTemplate.update("update  dzqh_ground_motion_parameters  set gmp_code=?,id=?,zoning_code=?,province=?,city=?,county=?,township=?,longitude=?,latitude=?,peak_acceleration=?,response_spectrum=?,reason_for_change=?,approval_documents=?,update_user=?,update_time=?,is_delete=?,type=?,list_type=?,original_id=?"
                        +" where gmp_id=?",
               entity.getGmpCode(),entity.getId(),entity.getZoningCode(),entity.getProvince(),entity.getCity(),entity.getCounty(),entity.getTownship(),entity.getLongitude(),entity.getLatitude(),entity.getPeakAcceleration(),entity.getResponseSpectrum(),entity.getReasonForChange(),entity.getApprovalDocuments(),entity.getUpdateUser(),entity.getUpdateTime(),entity.getIsDelete(),entity.getType(),entity.getListType(),entity.getOriginalId(),
               entity.getGmpId()
        );
    }

    @Override
    public List<Long> getAllId() {
        String sql = "select id from dzqh_ground_motion_parameters";
        List<Long> counts = jdbcTemplate.query(sql, new RowMapper<Long>() {
            @Override
            public Long mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs.getLong(1);
            }
        });
        return counts;
    }

    @Override
    public DzqhGroundMotionParametersEntity getGmpById(Long id) {
        try {
            String sql = "select * from dzqh_ground_motion_parameters where id = '" + id + "' and is_delete = '0'";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(DzqhGroundMotionParametersEntity.class));
        } catch (DataAccessException e) {
            return null;
        }
    }

    @Override
    public List<DzqhGroundMotionParametersEntity> getMotionParameters(MotionParametersParam param) {
        StringBuilder sql = new StringBuilder("select * from dzqh_ground_motion_parameters where is_delete = '0'");
        if(PlatformObjectUtils.isNotEmpty(param.getListType())){
            sql.append(" and list_type = '" + param.getListType() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getZoningCode())) {
            sql.append(" and zoning_code = '" + param.getZoningCode() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getProvince())) {
            sql.append(" and province = '" + param.getProvince() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCity())) {
            sql.append(" and city = '" + param.getCity() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCounty())) {
            sql.append(" and county = '" + param.getCounty() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getTownship())) {
            sql.append(" and township = '" + param.getTownship() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getReasonForChange())) {
            sql.append(" and reason_for_change like '%" + param.getReasonForChange() + "%'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getStartLatitude()) && PlatformObjectUtils.isNotEmpty(param.getEndLatitude())) {
            sql.append(" and latitude between " + param.getStartLatitude() + " and " + param.getEndLatitude());
        }
        if (PlatformObjectUtils.isNotEmpty(param.getStartLongitude()) && PlatformObjectUtils.isNotEmpty(param.getEndLongitude())) {
            sql.append(" and longitude between " + param.getStartLongitude() + " and " + param.getEndLongitude());
        }
        List<DzqhGroundMotionParametersEntity> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhGroundMotionParametersEntity.class));
        return list;
    }

    @Override
    public List<DzqhGroundMotionParametersEntity> getByGeom(MotionParametersParam param,String geom) {
        StringBuilder sql = new StringBuilder("select * from dzqh_ground_motion_parameters where is_delete = '0'  ");
        if (PlatformObjectUtils.isNotEmpty(geom)) {
            sql.append(" and ST_Intersects(st_geometryfromtext('POINT(' || longitude || ' ' || latitude || ')',4490),st_geometryfromtext('"+geom+"',4490))");
        }

        if (PlatformObjectUtils.isNotEmpty(param.getZoningCode())) {
            sql.append(" and zoning_code = '" + param.getZoningCode() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getProvince())) {
            sql.append(" and province = '" + param.getProvince() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCity())) {
            sql.append(" and city = '" + param.getCity() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCounty())) {
            sql.append(" and county = '" + param.getCounty() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getTownship())) {
            sql.append(" and township = '" + param.getTownship() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getReasonForChange())) {
            sql.append(" and reason_for_change like '%" + param.getReasonForChange() + "%'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getStartLatitude()) && PlatformObjectUtils.isNotEmpty(param.getEndLatitude())) {
            sql.append(" and latitude between " + param.getStartLatitude() + " and " + param.getEndLatitude());
        }
        if (PlatformObjectUtils.isNotEmpty(param.getStartLongitude()) && PlatformObjectUtils.isNotEmpty(param.getEndLongitude())) {
            sql.append(" and longitude between " + param.getStartLongitude() + " and " + param.getEndLongitude());
        }
        return jdbcTemplate.query(sql.toString(),new BeanPropertyRowMapper<>(DzqhGroundMotionParametersEntity.class));
    }
}
